package eu.log.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Collection;
import java.util.Date;

import eu.data.util.RollFetchDataSource;
import eu.log.Log;
import eu.log.Rank;
import eu.log.Storage;

public class StorageOverDataBase implements Storage {
	String db=null;
	String table=null;
//	Integer id=-1;
	
	StorageOverDataBase(String db,String table) {
		this.db=db;
		this.table=table;
		Helper.createDatabase(db);
		Helper.createTable(db, table);
	}

	@Override
	public void add(Log log) {
		PreparedStatement pst=getStatement("insert into "+table+" values(?,?,?,?)");
		try {
			pst.setString(1, log.getClientIdentity());
			pst.setString(2, log.getEvent());
			pst.setTimestamp(3, new Timestamp(log.getTime()));
			pst.setString(4, log.getRank().toString());
			pst.executeUpdate();
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void add(Collection<Log> logs) {
		PreparedStatement pst=getStatement("insert into "+table+" values(?,?,?,?,?)");
		try {
			for(Log log:logs){
				pst.setString(1, log.getClientIdentity());
				pst.setString(2, log.getEvent());
				pst.setTimestamp(3, new Timestamp(log.getTime()));
				pst.setString(4, log.getRank().toString());
				pst.addBatch();
			}
			pst.executeBatch();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public RollFetchDataSource<Log> getByPeriod(Date start, Date end) {
		String sql="select * from "+table+" where time>=? and time<=?";
		PreparedStatement pst=getStatement(sql);
		try {
			pst.setTimestamp(1, new Timestamp(start.getTime()));
			pst.setTimestamp(2, new Timestamp(end.getTime()));
			return new QueryDataSource(pst.executeQuery());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public RollFetchDataSource<Log> getByPeriod(long start, long end) {
		String sql="select * from "+table+" where time>=? and time<=?";
		PreparedStatement pst=getStatement(sql);
		try {
			pst.setTimestamp(1, new Timestamp(start));
			pst.setTimestamp(2, new Timestamp(end));
			return new QueryDataSource(pst.executeQuery());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public RollFetchDataSource<Log> getByClient(String clientIdentity) {
		String sql="select * from "+table+" where clientIdentity=?";
		PreparedStatement pst=getStatement(sql);
		try {
			pst.setString(1, clientIdentity);
			return new QueryDataSource(pst.executeQuery());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public RollFetchDataSource<Log> getByRank(Rank rank) {
		String sql="select * from "+table+" where rank=?";
		PreparedStatement pst=getStatement(sql);
		try {
			pst.setString(1, rank.toString());
			return new QueryDataSource(pst.executeQuery());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public void close() {
		// TODO Auto-generated method stub
		
	}

	@Override
	public Log newLog() {
		return new LogEntity();
	}

	@Override
	public Log newLog(String clientIdentity, String event, long time, Rank rank) {
		return new LogEntity(clientIdentity, event, time, rank);
	}

	private PreparedStatement getStatement(String sql){
		try {
			return Helper.getConnection("jdbc:smallsql:"+db).prepareStatement(sql, 
					ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY );
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public RollFetchDataSource<Log> getAll() {
		String sql="select * from "+table;
		PreparedStatement pst=getStatement(sql);
		try {
			return new QueryDataSource(pst.executeQuery());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
}
